package com.wxtf.platform.tools;

import java.sql.*;
import java.util.*;

import net.sf.json.JSONArray;

import org.apache.commons.id.Hex;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.*;
import org.hibernate.impl.SessionImpl;
import org.springframework.orm.hibernate3.HibernateCallback;

/**
 * @author 黄威
 * **/
public class AccessDataBaseUtil {
	private static Log log = LogFactory.getLog(AccessDataBaseUtil.class);
	
	private static String key = "e10adc3949ba59abbe56e057f20f883e";
	private static String pageSqlPrefix = "select wxtf.* from (select thtf.*, rownum num from ( ";
	private static String pageSqlSuffix = " ) thtf) wxtf where ? < num and num <= ?";

	public static String getKey(){
		return key;
	}
	
	private static String getPageSql(String sql) throws Exception {
		StringBuffer buff = new StringBuffer(pageSqlPrefix);
		buff.append(sql).append(pageSqlSuffix);
		return buff.toString();
	}

	public static String getPKUUID() {
		return new String(Hex.encodeHex(org.apache.commons.id.uuid.UUID.randomUUID().getRawBytes()));
	}
	
	/**
	 * 描述：query4SQLPagging sql分页 支持mysql与oracle
	 * 
	 * @param fields 字段名称
	 * @param countSql 行数SQL
	 * @param querySql 数据SQL
	 * @param start 起始页
	 * @param limit 每页行数
	 * @param params 参数
	 * @return
	 * @CreateOn Jul 5, 2012 4:46:45 PM
	 * @author chun_chang
	 */
	public static String query4SQLPagging(final String[] fields, final String countSql,String querySql, final int start,
			final int limit, final Object[] params){
		if(null == fields || fields.length <= 0) {
			return "{\"Total\":\"0\",\"Rows\":[]}";
		}
		
		if(StringUtils.isBlank(countSql) || StringUtils.isBlank(querySql) || start < 0 || limit < 0) {
			return "{\"Total\":\"0\",\"Rows\":[]}";
		}
		String dbtype = "";
		int Total = 0;
		String json = "[]";
		JSONArray jsonArray = null;
		Session session = null;
		try{
			session = ConstantInfoHelper.hnbd.getHibernateTemplate().getSessionFactory().openSession();
			String dialect = ((SessionImpl)session).getFactory().getDialect().getClass().getName(); 
			if(dialect.toLowerCase().indexOf("mysql") > 0) {
				querySql += " limit ?, ?";
				dbtype = "mysql";
			} else if(dialect.toLowerCase().indexOf("oracle") > 0) {
				dbtype = "oracle";
				querySql = getPageSql(querySql);
			} else {
				return "{\"Total\":\"0\",\"Rows\":[]}";
			}
			//取出总条数
			Total = queryForInt(countSql, params);
			if (Total <= 0) {
				return "{\"Total\":\"0\",\"Rows\":[]}";
			} else {
				Object[] param = null;
				if(params == null) {
					param = new Object[2];
				} else {
					param = new Object[params.length + 2];
					for(int i = 0; i < params.length; i++) {
						param[i] = params[i];
					}
				}
				if("mysql".equals(dbtype)) {
					param[param.length - 2] = (start - 1) * limit;
					param[param.length - 1] = limit;
				} else if("oracle".equals(dbtype)) {
					param[param.length - 2] = start - 1;
					param[param.length - 1] = start + limit - 1;
				}
				List<Map<String, String>> list = query(fields, querySql, param);
				jsonArray = JSONArray.fromObject(list);
				json = jsonArray.toString();
				return "{\"Total\":\"" + Total + "\",\"Rows\":" + json + "}";
			}
		}catch(Exception e){
			log.error(e, e);
		} finally{
			session.flush();
			session.close();
		}
		return "{\"Total\":\"0\",\"Rows\":[]}";
	}
	
	public static List<Map<String, String>> query4SQLPaggings(final String[] fields, final String countSql,String querySql, final int start,
			final int limit, Object[] count){
		List<Map<String, String>> list= new ArrayList<Map<String, String>>();
		if(null == fields || fields.length <= 0) {
			return list;
		}
		
		if(StringUtils.isBlank(countSql) || StringUtils.isBlank(querySql) || start < 0 || limit < 0) {
			return list;
		}
		String dbtype = "";
		int Total = 0;
		Session session = null;
		try{
			session = ConstantInfoHelper.hnbd.getHibernateTemplate().getSessionFactory().openSession();
			String dialect = ((SessionImpl)session).getFactory().getDialect().getClass().getName(); 
			if(dialect.toLowerCase().indexOf("mysql") > 0) {
				querySql += " limit ?, ?";
				dbtype = "mysql";
			} else if(dialect.toLowerCase().indexOf("oracle") > 0) {
				dbtype = "oracle";
				querySql = getPageSql(querySql);
			} else {
				return list;
			}
			//取出总条数
			Total = queryForInt(countSql, null);
			count[0] = Total;
			if (Total <= 0) {
				return list;
			} else {
				Object[] param = null;
				param = new Object[2];
				if("mysql".equals(dbtype)) {
					param[param.length - 2] = (start - 1) * limit;
					param[param.length - 1] = limit;
				} else if("oracle".equals(dbtype)) {
					param[param.length - 2] = start - 1;
					param[param.length - 1] = start + limit - 1;
				}
				list = query(fields, querySql, param);
				return list;
			}
		} catch(Exception e) {
			log.error(e, e);
		} finally {
			session.flush();
			session.close();
		}
		return list;
	}
      
	/** 
	 * 描述：queryForInt 获取SQL查询结果的行数
	 * @param sql
	 * @param params
	 * @return
	 * @CreateOn Jul 5, 2012  4:39:51 PM
	 * @author chun_chang
	 * @throws Exception 
	 */
	public static int queryForInt(final String sql, final Object[] params) throws Exception {
		try {
			Object obj = ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					int val = 0;
					SQLQuery query = session.createSQLQuery(sql);
					if(null != params) {
						for(int i = 0; i < params.length; i++) {
							query.setParameter(i, params[i]);
						}
					}
					List<?> list = query.list();
					if(null != list && list.size() > 0) {
						Object obj = list.get(0);
						val = Integer.valueOf(String.valueOf(obj));
					}
					session.flush();
					session.close();
					return val;
				}
				
			});
			return (Integer)obj;
		} catch(Exception e) {
			log.error(e, e);
		} finally {
		}
		return 0;
	}
	
	/**
	 * 描述：query4List 查询一个字段的时候用
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @CreateOn Jul 10, 2012 1:41:00 PM
	 * @author chun_chang
	 * @throws Exception
	 */
	public static List<Object> query4List(final String sql, final Object[] params) throws Exception {
		List<Object> list = null;
		try {
			Object obj = ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					List<Object> list = new ArrayList<Object>();
					
					SQLQuery query = session.createSQLQuery(sql);
					if(null != params) {
						for(int i = 0; i < params.length; i++) {
							query.setParameter(i, params[i]);
						}
					}
					List<?> list1 = query.list();
					if(null != list1 && list1.size() > 0) {
						for(int i = 0; i < list1.size(); i++) {
							Object obj = list1.get(i);
							list.add((null == obj) ? "" : obj);
						}
					}
					
					session.flush();
					session.close();
					return list;
				}
				
			});
			list = (List<Object>) obj;
		} catch(Exception e) {
			log.error(e, e);
		} finally {
			
		}
		return list;
	}
	
	
	/** 
	 * 描述：query 
	 * @param jsonField json串中的key
	 * @param querySql sql语句
	 * @param params 参数
	 * @return
	 * @CreateOn Jul 5, 2012  4:40:26 PM
	 * @author chun_chang
	 * @throws Exception 
	 */
	public static List<Map<String, String>> query(final String[] jsonField, final String querySql, final Object[] params) throws Exception{
		List<Map<String, String>> list = null;
		try {
			Object obj = ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					List<Map<String, String>> list = new ArrayList<Map<String, String>>();
					SQLQuery query = session.createSQLQuery(querySql);
					if(null != params) {
						for(int i = 0; i < params.length; i++) {
							query.setParameter(i, params[i]);
						}
					}
					List<?> list1 = query.list();
					
					if(null != list1 && list1.size() > 0) {
						for(int i = 0; i < list1.size(); i++) {
							Map<String, String> map = new HashMap<String, String>();
							Object[] obj = (Object[]) list1.get(i);
							for(int j = 0; j < obj.length; j++) {
								obj[j] = (null == obj[j]) ? "" : obj[j];
								map.put(jsonField[j], String.valueOf(obj[j]));
							}
							list.add(map);
						}
					}

					session.flush();
					session.close();
					
					return list;
				}
			});
			list = (List<Map<String, String>>) obj;
			
			
		} catch (Exception e) {
			log.error(e, e);
		} finally{
		}
		return list;
	}
	
	public static List<Map<String, Object>> query4ListMap(final String[] jsonField, final String querySql, final Object[] params) throws Exception{
		List<Map<String, Object>> list = null;
		try {
			Object obj = ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
					SQLQuery query = session.createSQLQuery(querySql);
					if(null != params) {
						for(int i = 0; i < params.length; i++) {
							query.setParameter(i, params[i]);
						}
					}
					List<?> list1 = query.list();
					
					if(null != list1 && list1.size() > 0) {
						for(int i = 0; i < list1.size(); i++) {
							Map<String, Object> map = new HashMap<String, Object>();
							Object[] obj = (Object[]) list1.get(i);
							for(int j = 0; j < obj.length; j++) {
								map.put(jsonField[j], (null == obj[j]) ? "" : obj[j]);
							}
							list.add(map);
						}
					}
					session.flush();
					session.close();
					return list;
				}
				
			});
			list = (List<Map<String, Object>>)obj;
		} catch (Exception e) {
			log.error(e, e);
		} finally{
			
		}
		return list;
	}
	
	/** 
	 * 描述：query 无key的形式，只返回数组
	 * @param querySql
	 * @param params
	 * @return
	 * @CreateOn Sep 6, 2012  9:57:20 AM
	 * @author chun_chang
	 * @throws Exception 
	 */
	public static List<Object[]> query(final String querySql, final Object[] params) throws Exception{
		List<Object[]> list = null;
		try {
			Object obj = ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					List<Object[]> list = new ArrayList<Object[]>();
					SQLQuery query = session.createSQLQuery(querySql);
					if(null != params) {
						for(int i = 0; i < params.length; i++) {
							query.setParameter(i, params[i]);
						}
					}
					List<?> list1 = query.list();
					if(null != list1 && list1.size() > 0) {
						for(int i = 0; i < list1.size(); i++) {
							Object[] obj = (Object[]) list1.get(i);
							for(int j=0;j<obj.length;j++){
								obj[j] = null == obj[j] ? "" : obj[j];
							}
							list.add(obj);
						}
					}
					session.flush();
					session.close();
					return list;
				}
				
			});
			list = (List<Object[]>)obj;
		} catch(Exception e) {
			log.error(e, e);
		} finally {
		}
		return list;
	}
	
	
	/** 
	 * 描述：executeSQLUpdate 单个sql的增删改
	 * @param sql
	 * @param params
	 * @throws Exception
	 * @CreateOn Jul 6, 2012  2:33:56 PM
	 * @author chun_chang
	 */
	public static void executeSQLUpdate(String sql) throws Exception {
		executeSQLUpdate(sql, (Object[])null);
	}
	public static void executeSQLUpdate(final String sql, final Object[] params) throws Exception {
		try {
			ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					SQLQuery query = session.createSQLQuery(sql);
					if(null != params){
						for(int i = 0; i < params.length; i++) {
							query.setParameter(i, params[i]);
						}
					}
					query.executeUpdate();
					session.flush();
					session.close();
					return null;
				}
				
			});
		} catch (Exception ex) {
			throw ex;
		} finally{
		}
	}
	
	public static void deleteById(String sql, Object id) throws Exception {
		deleteByIds(sql, new Object[] { id });
	}
	public static void deleteByIds(final String sql, final Object[] ids) throws Exception {
		try {
			ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					if(null != ids){
						for(int i = 0; i < ids.length; i++) {
							SQLQuery query = session.createSQLQuery(sql);
							query.setParameter(0, ids[i]);
							query.executeUpdate();
						}
					}
					session.flush();
					session.close();
					return null;
				}
				
				
			});
		} catch (Exception ex) {
			throw ex;
		} finally{
		}
	}
	public static void deleteByIds(final String sql, final List<Object> list) throws Exception {
		try {
			ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					if(null != list && list.size() > 0) {
						for(int i = 0; i < list.size(); i++) {
							Object id = list.get(i);
							SQLQuery query = session.createSQLQuery(sql);
							query.setParameter(0, id);
							query.executeUpdate();
						}
					}
					session.flush();
					session.close();
					return null;
				}

			});
		} catch (Exception ex) {
			throw ex;
		} finally {
		}
	}
	/** 
	 * 描述：executeSQLUpdate 批量增删改
	 * @param sql
	 * @param paramsList
	 * @throws Exception
	 * @CreateOn Jul 6, 2012  2:29:19 PM
	 * @author chun_chang
	 */
	public static int executeSQLUpdate(final String sql, final List<Object[]> paramsList) throws Exception{
		try {
			Object obj = ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					int result = 0;
					for(int i1 = 0; i1 < paramsList.size(); i1++) {
						SQLQuery query = session.createSQLQuery(sql);
						if(null != paramsList && paramsList.size() > 0) {
							Object[] params = paramsList.get(i1);
							for(int j = 0; j < params.length; j++) {
								query.setParameter(j, params[j]);
							}
						}
						result = query.executeUpdate();
					}
					session.flush();
					session.close();
					return result;
				}

			});
			return (Integer)obj;
		} catch (Exception ex) {
			throw ex;
		} finally {
		}
	}
	
	public static int[] executeSQLUpdate(final List<String> sqlList) throws Exception {
		int[] iRe = null;
		try {
			Object obj = ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					int[] iRe = null;
					if(sqlList.size() > 0) {
						iRe = new int[sqlList.size()];
					}
					for(int i1 = 0; i1 < sqlList.size(); i1++) {
						String sql = sqlList.get(i1);
						if(StringUtils.isNotBlank(sql)) {
							iRe[i1] = session.createSQLQuery(sql).executeUpdate();
						}
					}
					session.flush();
					session.close();
					return iRe;
				}

			});
			iRe = (int[])obj;
		} catch (Exception ex) {
			throw ex;
		} finally {
		}
		return iRe;
	}
	
    /***************************************************************************
	 * 处理多个sql的增删改，返回受影响的记录行数。
	 **************************************************************************/
	public static int[] executeSQLUpdate(final String updateOrDeleteSQLString[]) throws Exception{
		int[] iRe = null;
		try {
			Object obj = ConstantInfoHelper.hnbd.getHibernateTemplate().execute(new HibernateCallback(){

				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException{
					int[] iRe = null;
					if(updateOrDeleteSQLString.length > 0) {
						iRe = new int[updateOrDeleteSQLString.length];
					}
					for(int i1 = 0; i1 < updateOrDeleteSQLString.length; i1++) {
						if(StringUtils.isNotBlank(updateOrDeleteSQLString[i1])) {
							iRe[i1] = session.createSQLQuery(updateOrDeleteSQLString[i1]).executeUpdate();
						}
					}
					session.flush();
					session.close();
					return iRe;
				}

			});
			iRe = (int[])obj;
		} catch (Exception ex) {
			throw ex;
		}
		return iRe;
	}
	
	
}
